package br.com.ffm.praticas.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import br.com.ffm.praticas.bean.Country;
import br.com.ffm.praticas.connection.ConnectionDB;

public class CountryDAO {
	
	/**
	 * Method that returns all Countries
	 * @return List<Country>
	 * @throws SQLException
	 */
	public List<Country> getCountries() throws SQLException {
		String query = "SELECT * FROM country ORDER BY description";
		List<Country> list = new LinkedList<Country>();
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		while (result.next()) {
			Country country = new Country();
			country.setId(result.getInt("id"));
			if (result.getString("description") != null)
				country.setDescription(result.getString("description"));
			if (result.getString("printable_name") != null)
				country.setPrintableName(result.getString("printable_name"));
			if (result.getString("iso3") != null)
				country.setIso3(result.getString("iso3"));
			list.add(country);
		}
		ConnectionDB.getInstance().close();
		return list;
	}
	
	/**
	 * Method that returns a specific Country for your id
	 * @param id Country Identifier
	 * @return Country
	 * @throws SQLException
	 */
	public Country getCountry(int id) throws SQLException {
		String query = "SELECT * FROM country WHERE (id = " + id + ")";
		Country country;
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		if (result.next()) {
			country = new Country();
			country.setId(result.getInt("id"));
			if (result.getString("description") != null)
				country.setDescription(result.getString("description"));
			if (result.getString("printable_name") != null)
				country.setPrintableName(result.getString("printable_name"));
			if (result.getString("iso3") != null)
				country.setIso3(result.getString("iso3"));
			ConnectionDB.getInstance().close();
			return country;
		}
		ConnectionDB.getInstance().close();
		return null;
	}
	
	/**
	 * Method that returns a specific Country for your description
	 * @param  description Country Description
	 * @return Country
	 * @throws SQLException
	 */
	public Country getCountry(String description) throws SQLException {
		String query = "SELECT * FROM country WHERE (description = '" + description + "')";
		Country country;
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		if (result.next()) {
			country = new Country();
			country.setId(result.getInt("id"));
			if (result.getString("description") != null)
				country.setDescription(result.getString("description"));
			if (result.getString("printable_name") != null)
				country.setPrintableName(result.getString("printable_name"));
			if (result.getString("iso3") != null)
				country.setIso3(result.getString("iso3"));
			ConnectionDB.getInstance().close();
			return country;
		}
		ConnectionDB.getInstance().close();
		return null;
	}

}
